---CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
---AS 'C:/PostgreSQL/8.3/lib/plpgsql.dll', 'plpgsql_call_handler'
---LANGUAGE c;
	
----ALTER FUNCTION public.plpgsql_call_handler() OWNER TO joselvr;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner: 
--

---CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;


---
---otra forma es: colocar 
-----createlang -U postgres plpgsql DBname;
---createlang es una utilidad para a�adir un nuevo lenguaje de programaci�n a una base de datos PostgreSQL. createlang es s�lo un contenedor del CREATE LANGUAGE comando.

---/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////--------
DROP TRIGGER IF EXISTS insertar_proyecto_en_calificacion ON proyecto CASCADE;
DROP FUNCTION IF EXISTS insertar_proyecto_en_calificacion() ;
DROP TRIGGER IF EXISTS no_borrar_proyecto ON proyecto CASCADE;
DROP FUNCTION IF EXISTS no_borrar_proyecto();
DROP TRIGGER IF EXISTS promedio_proyecto ON evaluacion_proyecto CASCADE;
DROP FUNCTION IF EXISTS promedio_proyecto() ;




-- cuando se crea un proyecto o un usuario registra un proyecto se crea el proyecto en calificacion_promedio_proyecto
--esto es necesario para poder que funciones el  trigger promedio_proyecto();

	CREATE FUNCTION insertar_proyecto_en_calificacion() 
	RETURNS TRIGGER
	AS $$

	DECLARE
	
	BEGIN
	
	 	 INSERT INTO calificacion_promedio_proyecto (codigo_proyecto,calificacion_total,documentacion_completa,objetivos_compania,presupuesto_objetivos_proyecto
					,duracion_adecuada,rentabilidad_objetivos_compania,viable_financieramente,plan_de_riesgos
					,cronograma_adecuado,viable_tecnicamente,se_recomienda,observaciones,evaluadores_asignados,evaluadores_X_calificar)
					VALUES(new.codigo,0,0,0,0,0,0,0,0,0,0,0,'#',0,0);--el # es para saber cuado inicia y termina las observaciones de un evaluador
		
	  RETURN NULL;
	      
  	END;
	
	$$ LANGUAGE plpgsql;


	CREATE TRIGGER insertar_proyecto_en_calificacion AFTER INSERT
    ON proyecto FOR EACH ROW
    EXECUTE PROCEDURE insertar_proyecto_en_calificacion();	

---/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////--------

--- este evita que un proyecto se pueda borrar esto se hace solo para guardar consistencia en la bases de datos
--ya que si al usuario no se le da la opcion de borrar el proyecto pues nunca lo va hacer 

	CREATE FUNCTION no_borrar_proyecto() 
	RETURNS TRIGGER
	AS $$

	DECLARE
	
	BEGIN
		  
	  RAISE EXCEPTION 'un proyecto ya registrado no se puede eliminar';
       RETURN NULL;
    
   
	END;
	
	$$ LANGUAGE plpgsql;


	CREATE TRIGGER no_borrar_proyecto BEFORE DELETE
    ON proyecto FOR STATEMENT
    EXECUTE PROCEDURE no_borrar_proyecto() ;	




---/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////--------



--- se encarga de ir sumando los evaluadores y los actualiza en la tabla calificacion_promedio_proyecto que se van asignado a un proyecto
-- tambien saca el promedio de cada item y lo guarda en la tabla calificacion_promedio_proyecto
--solo suma el total cuando el numero de los evaluadores que faltan por evaluar sean 0


CREATE OR REPLACE FUNCTION  promedio_proyecto() 
RETURNS TRIGGER 
AS $$
 
DECLARE
  asignados INTEGER;
  xcalificar INTEGER;
  row_data RECORD;
  row_data2 RECORD;
  suma NUMERIC(10,2);
    
  BEGIN
   
	  IF (TG_OP = 'INSERT' ) THEN
	   -- cuenta cuandos evaluadores hay asignados y cuantos hay por evaluar
		  	asignados :=  (SELECT COUNT(evaluado) FROM evaluacion_proyecto WHERE codigo_proyecto=NEW.codigo_proyecto);
			xcalificar :=  (SELECT COUNT(evaluado) FROM evaluacion_proyecto WHERE evaluado='no' AND codigo_proyecto=NEW.codigo_proyecto);
		-- actualiza los valores en la tabla calificacion_promedio_proyecto	
		   	UPDATE calificacion_promedio_proyecto SET evaluadores_asignados=asignados,evaluadores_X_calificar=xcalificar WHERE codigo_proyecto=NEW.codigo_proyecto;
		
	  ELSIF (TG_OP = 'UPDATE' ) THEN
	  -- cuenta cuandos evaluadores hay asignados y cuantos hay por evaluar
		   	asignados :=  (SELECT COUNT(evaluado) FROM evaluacion_proyecto WHERE codigo_proyecto=NEW.codigo_proyecto);
			xcalificar :=  (SELECT COUNT(evaluado) FROM evaluacion_proyecto WHERE evaluado='no' AND codigo_proyecto=NEW.codigo_proyecto);
	-- actualiza los valores en la tabla calificacion_promedio_proyecto		
			UPDATE calificacion_promedio_proyecto SET evaluadores_X_calificar=xcalificar WHERE codigo_proyecto=NEW.codigo_proyecto;
			
		--- se suma todos los items y se dividen por el numero de evaluadores asignados, asi cada iten ya tendria su valor promediado esto se guarda en una variable temporal de tipo RECORD
		----esto es para un solo pryecto, 
	     SELECT INTO row_data  codigo_proyecto, SUM(documentacion_completa)/asignados AS c1,SUM(objetivos_compania)/asignados AS c2,SUM(presupuesto_objetivos_proyecto)/asignados AS c3,SUM(duracion_adecuada)/asignados AS c4
		,SUM(rentabilidad_objetivos_compania)/asignados AS c5,SUM(viable_financieramente)/asignados AS c6,SUM(plan_de_riesgos)/asignados AS c7,SUM(cronograma_adecuado)/asignados AS c8
		,SUM(viable_tecnicamente)/asignados AS c9 ,SUM(se_recomienda)/asignados AS c10
		 FROM evaluacion_proyecto
         WHERE codigo_proyecto =NEW.codigo_proyecto
		 GROUP BY codigo_proyecto;
		
		 ---seleciono las observaciones que tiene el proyecto para concatenarlas depues con la observacion del nuevo evaluador, la consulta es guarada en una variable temporal de tipo RECORD
		 SELECT INTO row_data2 observaciones FROM calificacion_promedio_proyecto  WHERE codigo_proyecto =NEW.codigo_proyecto;
		 
		 ---actualisa la tabla calificacion_promedio_proyecto con los nuevos valores
		 UPDATE calificacion_promedio_proyecto SET documentacion_completa=row_data.c1,objetivos_compania=row_data.c2,presupuesto_objetivos_proyecto=row_data.c3
	                               ,duracion_adecuada=row_data.c4,rentabilidad_objetivos_compania=row_data.c5,viable_financieramente=row_data.c6,plan_de_riesgos=row_data.c7
								   ,cronograma_adecuado=row_data.c8,viable_tecnicamente=row_data.c9,se_recomienda=row_data.c10,observaciones=row_data2.observaciones||new.observaciones 
								   WHERE codigo_proyecto =NEW.codigo_proyecto;
								   
		-- evalua si los evaluadores que falta por calificar es 0, suma todos los valores de los items lo cuales ya estan promediados y guarda el valor 
		--en el campo calificacion_total			
		 IF(xcalificar=0)THEN
		 
		 suma=(row_data.c1+row_data.c2+row_data.c3+row_data.c4+row_data.c5+row_data.c6+row_data.c7+row_data.c8+row_data.c9+row_data.c10);
		 UPDATE calificacion_promedio_proyecto SET calificacion_total=suma  WHERE codigo_proyecto =NEW.codigo_proyecto;
		
         END IF; 		
		 END IF;
       
       RETURN NULL;

END;
$$
LANGUAGE plpgsql;


CREATE TRIGGER promedio_proyecto AFTER INSERT OR UPDATE
ON evaluacion_proyecto FOR EACH ROW
EXECUTE PROCEDURE promedio_proyecto() ;


